/**
 * @swagger
 * /api/asset/area-analysis:
 *   get:
 *     tags:
 *       - 资产管理
 *     summary: 获取资产区域分析数据
 *     description: 按区域统计资产分布、价值和使用情况
 *     parameters:
 *       - in: query
 *         name: startDate
 *         schema:
 *           type: string
 *           format: date
 *         description: 开始日期 (YYYY-MM-DD)
 *       - in: query
 *         name: endDate
 *         schema:
 *           type: string
 *           format: date
 *         description: 结束日期 (YYYY-MM-DD)
 *     responses:
 *       200:
 *         description: 成功获取区域分析数据
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 code:
 *                   type: integer
 *                   example: 200
 *                   description: 响应状态码
 *                 message:
 *                   type: string
 *                   example: "区域分析获取成功"
 *                   description: 响应消息
 *                 data:
 *                   type: object
 *                   properties:
 *                     buildingAreaDistribution:
 *                       type: object
 *                       properties:
 *                         description:
 *                           type: string
 *                           description: 建筑面积分布描述
 *                         data:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               range:
 *                                 type: string
 *                                 description: 面积范围
 *                               count:
 *                                 type: number
 *                                 description: 数量
 *                               percentage:
 *                                 type: string
 *                                 description: 占比
 *                     landAreaDistribution:
 *                       type: object
 *                       properties:
 *                         description:
 *                           type: string
 *                           description: 土地面积分布描述
 *                         data:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               range:
 *                                 type: string
 *                                 description: 面积范围
 *                               count:
 *                                 type: number
 *                                 description: 数量
 *                               percentage:
 *                                 type: string
 *                                 description: 占比
 *                     categoryAreaStats:
 *                       type: object
 *                       properties:
 *                         description:
 *                           type: string
 *                           description: 分类面积统计描述
 *                         data:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               category:
 *                                 type: string
 *                                 description: 资产类别
 *                               totalBuildingArea:
 *                                 type: number
 *                                 description: 总建筑面积
 *                               totalLandArea:
 *                                 type: number
 *                                 description: 总土地面积
 *                               avgBuildingArea:
 *                                 type: string
 *                                 description: 平均建筑面积
 *                               avgLandArea:
 *                                 type: string
 *                                 description: 平均土地面积
 *                               count:
 *                                 type: number
 *                                 description: 数量
 *                     areaValueAnalysis:
 *                       type: object
 *                       properties:
 *                         description:
 *                           type: string
 *                           description: 面积价值分析描述
 *                         data:
 *                           type: object
 *                           properties:
 *                             # 移除预测性内容：面积效率分析
 *                             totalBuildingArea:
 *                               type: number
 *                               description: 总建筑面积
 *                             totalLandArea:
 *                               type: number
 *                               description: 总土地面积
 *                             totalValue:
 *                               type: number
 *                               description: 总价值
 *                             avgValuePerSqmBuilding:
 *                               type: string
 *                               description: 每平米建筑面积平均价值
 *                             avgValuePerSqmLand:
 *                               type: string
 *                               description: 每平米土地面积平均价值
 *                     largeAreaAssets:
 *                       type: object
 *                       properties:
 *                         description:
 *                           type: string
 *                           description: 大面积资产描述
 *                         data:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               assetName:
 *                                 type: string
 *                                 description: 资产名称
 *                               category:
 *                                 type: string
 *                                 description: 资产类别
 *                               buildingArea:
 *                                 type: number
 *                                 description: 建筑面积
 *                               landArea:
 *                                 type: number
 *                                 description: 土地面积
 *                               currentValue:
 *                                 type: number
 *                                 description: 当前价值
 *                               valuePerSqm:
 *                                 type: string
 *                                 description: 每平米价值
 *       500:
 *         description: 服务器内部错误
 */
import { withCors } from '../../../lib/cors';
import { success, error as errorResponse, handleResponse } from '../../../lib/response';
import { query } from '../../../lib/db';

/**
 * 处理资产区域分析请求
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 */
async function handler(req, res) {
  try {
    const { startDate, endDate } = req.query;

    let whereClause = '';
    const params = [];

    if (startDate && endDate) {
      whereClause = 'WHERE PURCHASE_DATE BETWEEN ? AND ?';
      params.push(startDate, endDate);
    } else if (startDate) {
      whereClause = 'WHERE PURCHASE_DATE >= ?';
      params.push(startDate);
    } else if (endDate) {
      whereClause = 'WHERE PURCHASE_DATE <= ?';
      params.push(endDate);
    }
    
    // 基础面积统计
    const basicAreaQuery = `
      SELECT 
        COUNT(*) as totalAssets,
        COUNT(CASE WHEN CHEKMJ > 0 THEN 1 END) as assetsWithBuildingArea,
        COUNT(CASE WHEN FENTMJ > 0 THEN 1 END) as assetsWithLandArea,
        SUM(COALESCE(CHEKMJ, 0)) as totalBuildingArea,
        SUM(COALESCE(FENTMJ, 0)) as totalLandArea,
        AVG(COALESCE(CHEKMJ, 0)) as avgBuildingArea,
        AVG(COALESCE(FENTMJ, 0)) as avgLandArea,
        MIN(CASE WHEN CHEKMJ > 0 THEN CHEKMJ END) as minBuildingArea,
        MAX(CHEKMJ) as maxBuildingArea,
        MIN(CASE WHEN FENTMJ > 0 THEN FENTMJ END) as minLandArea,
        MAX(FENTMJ) as maxLandArea
      FROM wd_sec_assets12
      ${whereClause}
    `;

    const basicResult = await query(basicAreaQuery, params);
    const basicData = basicResult[0];

    // 面积区间分布 - 房屋车库面积
    const buildingAreaRangeQuery = `
      SELECT 
        CASE 
          WHEN COALESCE(CHEKMJ, 0) = 0 THEN '无面积'
          WHEN CHEKMJ <= 50 THEN '0-50平米'
          WHEN CHEKMJ <= 100 THEN '50-100平米'
          WHEN CHEKMJ <= 200 THEN '100-200平米'
          WHEN CHEKMJ <= 500 THEN '200-500平米'
          WHEN CHEKMJ <= 1000 THEN '500-1000平米'
          ELSE '1000平米以上'
        END as areaRange,
        COUNT(*) as assetCount,
        SUM(COALESCE(CHEKMJ, 0)) as totalArea,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY 
        CASE 
          WHEN COALESCE(CHEKMJ, 0) = 0 THEN '无面积'
          WHEN CHEKMJ <= 50 THEN '0-50平米'
          WHEN CHEKMJ <= 100 THEN '50-100平米'
          WHEN CHEKMJ <= 200 THEN '100-200平米'
          WHEN CHEKMJ <= 500 THEN '200-500平米'
          WHEN CHEKMJ <= 1000 THEN '500-1000平米'
          ELSE '1000平米以上'
        END
      ORDER BY 
        CASE 
          WHEN areaRange = '无面积' THEN 0
          WHEN areaRange = '0-50平米' THEN 1
          WHEN areaRange = '50-100平米' THEN 2
          WHEN areaRange = '100-200平米' THEN 3
          WHEN areaRange = '200-500平米' THEN 4
          WHEN areaRange = '500-1000平米' THEN 5
          ELSE 6
        END
    `;

    const buildingRangeResult = await query(buildingAreaRangeQuery, params);

    // 面积区间分布 - 土地分摊面积
    const landAreaRangeQuery = `
      SELECT 
        CASE 
          WHEN COALESCE(FENTMJ, 0) = 0 THEN '无面积'
          WHEN FENTMJ <= 50 THEN '0-50平米'
          WHEN FENTMJ <= 100 THEN '50-100平米'
          WHEN FENTMJ <= 200 THEN '100-200平米'
          WHEN FENTMJ <= 500 THEN '200-500平米'
          WHEN FENTMJ <= 1000 THEN '500-1000平米'
          ELSE '1000平米以上'
        END as areaRange,
        COUNT(*) as assetCount,
        SUM(COALESCE(FENTMJ, 0)) as totalArea,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY 
        CASE 
          WHEN COALESCE(FENTMJ, 0) = 0 THEN '无面积'
          WHEN FENTMJ <= 50 THEN '0-50平米'
          WHEN FENTMJ <= 100 THEN '50-100平米'
          WHEN FENTMJ <= 200 THEN '100-200平米'
          WHEN FENTMJ <= 500 THEN '200-500平米'
          WHEN FENTMJ <= 1000 THEN '500-1000平米'
          ELSE '1000平米以上'
        END
      ORDER BY 
        CASE 
          WHEN areaRange = '无面积' THEN 0
          WHEN areaRange = '0-50平米' THEN 1
          WHEN areaRange = '50-100平米' THEN 2
          WHEN areaRange = '100-200平米' THEN 3
          WHEN areaRange = '200-500平米' THEN 4
          WHEN areaRange = '500-1000平米' THEN 5
          ELSE 6
        END
    `;

    const landRangeResult = await query(landAreaRangeQuery, params);

    // 按分类统计面积
    const categoryAreaQuery = `
      SELECT 
        COALESCE(ASSETS_SORT_GGJ_NAME, '未分类') as categoryName,
        COUNT(*) as assetCount,
        SUM(COALESCE(CHEKMJ, 0)) as totalBuildingArea,
        SUM(COALESCE(FENTMJ, 0)) as totalLandArea,
        AVG(COALESCE(CHEKMJ, 0)) as avgBuildingArea,
        AVG(COALESCE(FENTMJ, 0)) as avgLandArea,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY ASSETS_SORT_GGJ_NAME
      HAVING (totalBuildingArea > 0 OR totalLandArea > 0)
      ORDER BY (totalBuildingArea + totalLandArea) DESC
      LIMIT 10
    `;

    const categoryResult = await query(categoryAreaQuery, params);

    // 面积与价值相关性分析
    const correlationQuery = `
      SELECT 
        COUNT(CASE WHEN CHEKMJ > 0 AND STOCK_PRICE > 0 THEN 1 END) as validDataCount,
        SUM(COALESCE(CHEKMJ, 0) * COALESCE(STOCK_PRICE, 0)) as areaValueProduct,
        SUM(COALESCE(CHEKMJ, 0)) as totalBuildingArea,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        SUM(COALESCE(STOCK_PRICE, 0)) / NULLIF(SUM(COALESCE(CHEKMJ, 0)), 0) as valuePerSquareMeter
      FROM wd_sec_assets12
      WHERE CHEKMJ > 0 AND STOCK_PRICE > 0 ${whereClause ? 'AND ' + whereClause.replace('WHERE ', '') : ''}
    `;

    const correlationResult = await query(correlationQuery, params);
    const correlationData = correlationResult[0];

    // 大面积资产分析
    const largeAreaQuery = `
      SELECT 
        COUNT(CASE WHEN CHEKMJ >= 500 THEN 1 END) as largeBuildingAssets,
        COUNT(CASE WHEN FENTMJ >= 500 THEN 1 END) as largeLandAssets,
        SUM(CASE WHEN CHEKMJ >= 500 THEN COALESCE(CHEKMJ, 0) END) as largeBuildingArea,
        SUM(CASE WHEN FENTMJ >= 500 THEN COALESCE(FENTMJ, 0) END) as largeLandArea,
        SUM(CASE WHEN CHEKMJ >= 500 THEN COALESCE(STOCK_PRICE, 0) END) as largeBuildingValue,
        SUM(CASE WHEN FENTMJ >= 500 THEN COALESCE(STOCK_PRICE, 0) END) as largeLandValue
      FROM wd_sec_assets12
      ${whereClause}
    `;

    const largeAreaResult = await query(largeAreaQuery, params);
    const largeAreaData = largeAreaResult[0];

    const responseData = {
      overview: {
        title: "面积统计概览",
        statistics: {
          totalAssets: parseInt(basicData.totalAssets),
          assetsWithBuildingArea: parseInt(basicData.assetsWithBuildingArea),
          assetsWithLandArea: parseInt(basicData.assetsWithLandArea),
          buildingAreaRatio: ((parseInt(basicData.assetsWithBuildingArea) / parseInt(basicData.totalAssets)) * 100).toFixed(2),
          landAreaRatio: ((parseInt(basicData.assetsWithLandArea) / parseInt(basicData.totalAssets)) * 100).toFixed(2)
        },
        totals: {
          totalBuildingArea: parseFloat(basicData.totalBuildingArea || 0).toFixed(2),
          totalLandArea: parseFloat(basicData.totalLandArea || 0).toFixed(2),
          avgBuildingArea: parseFloat(basicData.avgBuildingArea || 0).toFixed(2),
          avgLandArea: parseFloat(basicData.avgLandArea || 0).toFixed(2)
        },
        ranges: {
          minBuildingArea: parseFloat(basicData.minBuildingArea || 0).toFixed(2),
          maxBuildingArea: parseFloat(basicData.maxBuildingArea || 0).toFixed(2),
          minLandArea: parseFloat(basicData.minLandArea || 0).toFixed(2),
          maxLandArea: parseFloat(basicData.maxLandArea || 0).toFixed(2)
        }
      },
      buildingAreaDistribution: {
        title: "房屋车库面积分布",
        ranges: buildingRangeResult.map(row => ({
          areaRange: row.areaRange,
          assetCount: parseInt(row.assetCount),
          totalArea: parseFloat(row.totalArea || 0).toFixed(2),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          percentage: ((parseInt(row.assetCount) / parseInt(basicData.totalAssets)) * 100).toFixed(2),
          valuePerSquareMeter: parseFloat(row.totalArea) > 0 ? 
            (parseFloat(row.totalValue) / parseFloat(row.totalArea)).toFixed(2) : "0.00"
        }))
      },
      landAreaDistribution: {
        title: "土地分摊面积分布",
        ranges: landRangeResult.map(row => ({
          areaRange: row.areaRange,
          assetCount: parseInt(row.assetCount),
          totalArea: parseFloat(row.totalArea || 0).toFixed(2),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          percentage: ((parseInt(row.assetCount) / parseInt(basicData.totalAssets)) * 100).toFixed(2),
          valuePerSquareMeter: parseFloat(row.totalArea) > 0 ? 
            (parseFloat(row.totalValue) / parseFloat(row.totalArea)).toFixed(2) : "0.00"
        }))
      },
      categoryAreaStats: {
        title: "分类面积统计(Top10)",
        categories: categoryResult.map(row => ({
          categoryName: row.categoryName,
          assetCount: parseInt(row.assetCount),
          totalBuildingArea: parseFloat(row.totalBuildingArea || 0).toFixed(2),
          totalLandArea: parseFloat(row.totalLandArea || 0).toFixed(2),
          avgBuildingArea: parseFloat(row.avgBuildingArea || 0).toFixed(2),
          avgLandArea: parseFloat(row.avgLandArea || 0).toFixed(2),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          totalArea: (parseFloat(row.totalBuildingArea || 0) + parseFloat(row.totalLandArea || 0)).toFixed(2)
        }))
      },
      areaValueAnalysis: {
        title: "面积与价值相关性分析",
        correlation: {
          validDataCount: parseInt(correlationData.validDataCount || 0),
          valuePerSquareMeter: parseFloat(correlationData.valuePerSquareMeter || 0).toFixed(2),
          totalBuildingArea: parseFloat(correlationData.totalBuildingArea || 0).toFixed(2),
          totalValue: parseFloat(correlationData.totalValue || 0).toFixed(2)
        }
      },
      largeAreaAssets: {
        title: "大面积资产分析(≥500平米)",
        statistics: {
          largeBuildingAssets: parseInt(largeAreaData.largeBuildingAssets || 0),
          largeLandAssets: parseInt(largeAreaData.largeLandAssets || 0),
          largeBuildingArea: parseFloat(largeAreaData.largeBuildingArea || 0).toFixed(2),
          largeLandArea: parseFloat(largeAreaData.largeLandArea || 0).toFixed(2),
          largeBuildingValue: parseFloat(largeAreaData.largeBuildingValue || 0).toFixed(2),
          largeLandValue: parseFloat(largeAreaData.largeLandValue || 0).toFixed(2),
          largeBuildingRatio: ((parseInt(largeAreaData.largeBuildingAssets || 0) / parseInt(basicData.totalAssets)) * 100).toFixed(2),
          largeLandRatio: ((parseInt(largeAreaData.largeLandAssets || 0) / parseInt(basicData.totalAssets)) * 100).toFixed(2)
        }
      }
    };

    return handleResponse(res, success(responseData, "资产面积分析获取成功"));
    
  } catch (error) {
    console.error('Asset area analysis error:', error);
    return handleResponse(res, errorResponse("获取资产面积分析失败", 500));
  }
}

export default withCors(handler, ['GET']);